package webdev.database;

import java.util.ArrayList;

import webdev.dto.BootKingDTO;
import webdev.dto.CommentDTO;
import webdev.dto.DiaDiemDTO;
import webdev.dto.UserDTO;
import webdev.utils.ConvertUnsigned;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SQLQuery {

	DatabaseHelp mDatabaseHelp;

	public SQLQuery(Context context) {
		mDatabaseHelp = new DatabaseHelp(context);
	}

	// private void initDB() {
	// try {
	// mDatabaseHelp.createDataBase();
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// }

	public boolean addUser(UserDTO user) {
		boolean isOk = true;

		SQLiteDatabase db = mDatabaseHelp.openDataBase();

		ContentValues values = new ContentValues();
		values.put(InfoDB.USER_NAME, user.getmUserName());
		values.put(InfoDB.PASSWORD, user.getmPassword());

		// Inserting Row
		try {
			db.insert(InfoDB.TABLE_USER, null, values);
		} catch (Exception e) {
			isOk = false;
			e.printStackTrace();
		}
		db.close();

		return isOk;
	}

	public boolean updateUser(UserDTO user) {
		boolean isOk = true;

		try {
			SQLiteDatabase db = mDatabaseHelp.openDataBase();

			ContentValues values = new ContentValues();
			values.put(InfoDB.PASSWORD, user.getmPassword());

			// updating row
			db.update(InfoDB.TABLE_USER, values, InfoDB.USER_NAME + " = ?",
					new String[] { String.valueOf(user.getmUserName()) });
			db.close();
		} catch (Exception e) {
			isOk = false;
		}
		return isOk;
	}

	public ArrayList<UserDTO> getAllUser() {
		ArrayList<UserDTO> users = new ArrayList<UserDTO>();
		// Select All Query
		String selectQuery = "SELECT * FROM " + InfoDB.TABLE_USER;
		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		UserDTO user;
		if (cursor.moveToFirst()) {
			do {
				user = new UserDTO();
				user.setmUserName(cursor.getString(0));
				user.setmPassword(cursor.getString(1));

				// Add object to list
				users.add(user);
			} while (cursor.moveToNext());
		} // end if

		cursor.close();
		db.close();
		return users;
	}

	public boolean addBooking(BootKingDTO booking) {
		boolean isOk = true;

		SQLiteDatabase db = mDatabaseHelp.openDataBase();

		ContentValues values = new ContentValues();
		values.put(InfoDB.ID_BOOKING, booking.getId());
		values.put(InfoDB.NAME_USER_BOOKING, booking.getNameUser());
		values.put(InfoDB.NAME_PLACE_BOOKING, booking.getNamePlace());
		values.put(InfoDB.TIME_ARRIVE_BOOKING, booking.getTimeArrive());
		values.put(InfoDB.TIME_LEAVE_BOOKING, booking.getTimeLeave());
		values.put(InfoDB.CONTENT_BOOKING, booking.getNote());

		// Inserting Row
		try {
			db.insert(InfoDB.TABLE_BOOKING, null, values);
		} catch (Exception e) {
			isOk = false;
			e.printStackTrace();
		}
		db.close();

		return isOk;
	}

	public ArrayList<BootKingDTO> getAllBookingOfUser4Place(String userName) {
		ArrayList<BootKingDTO> bookings = new ArrayList<BootKingDTO>();

		String query = "SELECT * FROM TABLE_BOOKING WHERE (user_name_booking='"
				+ userName + "')";
		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		Cursor cursor = db.rawQuery(query, null);

		BootKingDTO booking;
		if (cursor.moveToFirst()) {
			do {
				booking = new BootKingDTO();
				booking.setId(cursor.getInt(0));
				booking.setNameUser(cursor.getString(1));
				booking.setNamePlace(cursor.getString(2));
				booking.setTimeArrive(cursor.getString(3));
				booking.setTimeLeave(cursor.getString(4));
				booking.setNote(cursor.getString(5));

				// Add object to list
				bookings.add(booking);
			} while (cursor.moveToNext());
		} // end if

		cursor.close();
		db.close();
		return bookings;
	}

	public ArrayList<BootKingDTO> getAllBooking() {
		ArrayList<BootKingDTO> bookings = new ArrayList<BootKingDTO>();

		String query = "SELECT * FROM TABLE_BOOKING";
		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		Cursor cursor = db.rawQuery(query, null);

		BootKingDTO booking;
		if (cursor.moveToFirst()) {
			do {
				booking = new BootKingDTO();
				booking.setId(cursor.getInt(0));
				booking.setNameUser(cursor.getString(1));
				booking.setNamePlace(cursor.getString(2));
				booking.setTimeArrive(cursor.getString(3));
				booking.setTimeLeave(cursor.getString(4));
				booking.setNote(cursor.getString(5));

				// Add object to list
				bookings.add(booking);
			} while (cursor.moveToNext());
		} // end if

		cursor.close();
		db.close();
		return bookings;
	}
	
	public boolean addComment(CommentDTO comment) {
		boolean isOk = true;

		SQLiteDatabase db = mDatabaseHelp.openDataBase();

		ContentValues values = new ContentValues();
		values.put(InfoDB.ID_COMMENT, comment.getIdComment());
		values.put(InfoDB.USER_COMMENT, comment.getUserName());
		values.put(InfoDB.NAME_PLACE, comment.getNamePlace());
		values.put(InfoDB.CONTENT_COMMENT, comment.getContent());

		// Inserting Row
		try {
			db.insert(InfoDB.TABLE_COMMENT, null, values);
		} catch (Exception e) {
			isOk = false;
			e.printStackTrace();
		}
		db.close();

		return isOk;
	}

	public ArrayList<CommentDTO> getAllCommentOfUser4Place(String userName,
			String placeName) {
		ArrayList<CommentDTO> comments = new ArrayList<CommentDTO>();

		String query = "SELECT * FROM TABLE_COMMENT WHERE (user_name_comment='"
				+ userName + "') AND (name_place='" + placeName + "')";
		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		Cursor cursor = db.rawQuery(query, null);

		CommentDTO comment;
		if (cursor.moveToFirst()) {
			do {
				comment = new CommentDTO();
				comment.setIdComment(cursor.getInt(0));
				comment.setUserName(cursor.getString(1));
				comment.setNamePlace(cursor.getString(2));
				comment.setContent(cursor.getString(3));

				// Add object to list
				comments.add(comment);
			} while (cursor.moveToNext());
		} // end if

		cursor.close();
		db.close();
		return comments;
	}

	public static final int TYPE_HOTEL = 0;
	public static final int TYPE_PLACE = 1;
	public static final int TYPE_LU_HANH = 2;
	public static final int TYPE_FOOD = 3;
	public static final int TYPE_RELAX = 4;

	public ArrayList<DiaDiemDTO> getAllDiaDiem(int type) {
		ArrayList<DiaDiemDTO> diaDiems = new ArrayList<DiaDiemDTO>();
		try {
			// Select All Query
			String typeStr;
			if (type == 0) {
				typeStr = "Place";
			} else if (type == 1) {
				typeStr = "Hotel";
			} else if (type == 2) {
				typeStr = "LuHanh";
			} else if (type == 3) {
				typeStr = "Food";
			} else {
				typeStr = "Relax";
			}
			String selectQuery = "SELECT * FROM TABLE_DIA_DIEM WHERE (type='"
					+ typeStr + "')";
			SQLiteDatabase db = mDatabaseHelp.openDataBase();
			Cursor cursor = db.rawQuery(selectQuery, null);

			DiaDiemDTO diaDiem;
			if (cursor.moveToFirst()) {
				do {
					diaDiem = new DiaDiemDTO();
					diaDiem.setId(cursor.getInt(0));
					diaDiem.setTitle(cursor.getString(1));
					diaDiem.setDescription(cursor.getString(2));
					diaDiem.setAddress(cursor.getString(3));
					diaDiem.setPhoneNumber(cursor.getString(4));
					diaDiem.setImage(cursor.getString(5));
					diaDiem.setLat(cursor.getDouble(6));
					diaDiem.setLng(cursor.getDouble(7));

					// Add object to list
					diaDiems.add(diaDiem);
				} while (cursor.moveToNext());
			} // end if

			cursor.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return diaDiems;
	}
	
	
	public DiaDiemDTO getDiaDiemById(String idDiaDiem) {
		
		DiaDiemDTO diaDiem = null;
		
		try {
			String selectQuery = "SELECT * FROM TABLE_DIA_DIEM WHERE (id_dia_diem='"
					+ idDiaDiem + "')";
			SQLiteDatabase db = mDatabaseHelp.openDataBase();
			Cursor cursor = db.rawQuery(selectQuery, null);

			if (cursor.moveToFirst()) {
				do {
					diaDiem = new DiaDiemDTO();
					diaDiem.setId(cursor.getInt(0));
					diaDiem.setTitle(cursor.getString(1));
					diaDiem.setDescription(cursor.getString(2));
					diaDiem.setAddress(cursor.getString(3));
					diaDiem.setPhoneNumber(cursor.getString(4));
					diaDiem.setImage(cursor.getString(5));
					diaDiem.setLat(cursor.getDouble(6));
					diaDiem.setLng(cursor.getDouble(7));

					// Add object to list
				} while (cursor.moveToNext());
			} // end if

			cursor.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (diaDiem == null) {
			diaDiem = new DiaDiemDTO();
		}
		
		return diaDiem;
	}

	

	public ArrayList<DiaDiemDTO> getAllDiaDiemSearch(int type, String textSearch) {
		ArrayList<DiaDiemDTO> diaDiems = new ArrayList<DiaDiemDTO>();
		try {

			// Select All Query
			String typeStr;
			if (type == 0) {
				typeStr = "Place";
			} else if (type == 1) {
				typeStr = "Hotel";
			} else if (type == 2) {
				typeStr = "LuHanh";
			} else if (type == 3) {
				typeStr = "Food";
			} else {
				typeStr = "Relax";
			}
			String selectQuery = "SELECT * FROM TABLE_DIA_DIEM WHERE (type='"
					+ typeStr + "') and ( title_place_vietnamese like '%"
					+ textSearch + "%' )";
			System.out.println(selectQuery);
			SQLiteDatabase db = mDatabaseHelp.openDataBase();
			Cursor cursor = db.rawQuery(selectQuery, null);

			DiaDiemDTO diaDiem;
			if (cursor.moveToFirst()) {
				do {
					diaDiem = new DiaDiemDTO();
					diaDiem.setId(cursor.getInt(0));
					diaDiem.setTitle(cursor.getString(1));
					diaDiem.setDescription(cursor.getString(2));
					diaDiem.setAddress(cursor.getString(3));
					diaDiem.setPhoneNumber(cursor.getString(4));
					diaDiem.setImage(cursor.getString(5));
					diaDiem.setLat(cursor.getDouble(6));
					diaDiem.setLng(cursor.getDouble(7));

					// Add object to list
					diaDiems.add(diaDiem);
				} while (cursor.moveToNext());
			} // end if

			cursor.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return diaDiems;
	}

	public void setAllDiaDiem() {
		// Select All Query
		String selectQuery = "SELECT id_dia_diem, title_dia_diem FROM TABLE_DIA_DIEM";
		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				String id = cursor.getString(0);
				String title = cursor.getString(1);
				ConvertUnsigned clsConvert = new ConvertUnsigned();
				title = clsConvert.ConvertString(title);
				updateDataTable(id, title);
				System.out.println("Thuc hien xong " + id + " cau---");
				// Add object to list
			} while (cursor.moveToNext());
		} // end if

		cursor.close();
		db.close();
	}

	public void updateDataTable(String idcolum, String txtUpdate) {

		SQLiteDatabase db = mDatabaseHelp.openDataBase();
		ContentValues values = new ContentValues();
		values.put(InfoDB.DIA_DIEM_VIETNAMESE, txtUpdate);
		// Update Row
		try {
			long kq = db.update(InfoDB.TABLE_DIA_DIEM, values, "id_dia_diem='"
					+ idcolum + "'", null);
			System.out.println("kq update:" + kq);
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}
}
